package com.ddtou;


import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

public class MoonData {
	private static Connection conn = null;  
	private static Statement statement = null;  
	
	public MoonData(){
		if(conn==null){
			this.connSQL();
		}
		
	}

	/**
	 * @param args
	 */

	public static void main(String[] args) throws SQLException {

	  
	    // connect to MySQL  
		JSONObject json = new JSONObject();
		MoonData  m = new MoonData();
		m.TestDB();
}
	public List<Object> getList() throws SQLException{
		List<Object> list = new ArrayList<Object>();
		statement = conn.createStatement();
		String sql  = "SELECT ST_AsEWKT(geom) FROM alldata;";
		ResultSet rs = statement.executeQuery(sql);
		String str ;
		try {
			while(rs.next()){
				str =rs.getString(1);
				System.out.println(str);
				list.add(str);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.deconnSQL();
		}
		
		return list;
	}
	
	public List<Object> getDataListWithBbox(String bboxwkt,String type){
		List<Object> list = new ArrayList<Object>();
		
		String str ;
		try {
			statement = conn.createStatement();
			String sql  = "SELECT name,type,size, ST_AsEWKT(geom) FROM alldata where ST_Intersects(geom,ST_PolygonFromText('"
			+bboxwkt+"')) ";
			if(!type.equals("6")){
				sql+="and type='"+type+"'";
			}
			ResultSet rs = statement.executeQuery(sql);
			JSONObject obj ;
			while(rs.next()){
				obj = new JSONObject();
				str =rs.getString(3);
				obj.put("name", rs.getString(1));
				obj.put("type", rs.getString(2));
				obj.put("size", rs.getString(3));
				obj.put("geometry", rs.getString(4));
				System.out.println(str);
				list.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.deconnSQL();
		}
		
		return list;
	}
    private void connSQL() {  
		String urlpg = "jdbc:postgresql://localhost:5432/mymoon";  
        String username = "postgres";  
        String password = "q"; // 加载驱动程序以连接数据库   
        try {   
            Class.forName("org.postgresql.Driver").newInstance();   
            conn = DriverManager.getConnection( urlpg,username, password ); 
        }
        //捕获加载驱动程序异常  
         catch (Exception cnfex ) {  
             System.out.println("装载 JDBC/ODBC 驱动程序失败。" );  
         }
    }
    
    public void PostgreSQLJDBC() { 
      try { 
         Class.forName("org.postgresql.Driver"); 
         conn = DriverManager 
            .getConnection("jdbc:postgresql://localhost:5432/moondata", 
            "postgres", "q"); 
      } catch (Exception e) { 
         e.printStackTrace(); 
         System.err.println(e.getClass().getName()+": "+e.getMessage()); 
         System.exit(0); 
      } 
      System.out.println("Opened database successfully"); 
   } 

    
    public  void  TestDB()  {

    	String urlpg = "jdbc:postgresql://localhost:5432/moondata";
		String urlmysql = "jdbc:mysql://localhost:3307/moondata?characterEncoding=UTF-8";
        String username = "postgres"; 
        String usermysql = "root";  
        String password = "q"; // 加载驱动程序以连接数据库   
         try 
          {
            Class.forName( "com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:postgresql://localhost:5432/moondata" ;
            Connection con = DriverManager.getConnection(urlmysql, "root" , "q" );
            Statement st = con.createStatement();
            String sql = " select * from data" ;
            ResultSet rs = st.executeQuery(sql);
             while (rs.next())
             {
                System.out.print(rs.getString(1));
                //System.out.println(rs.getInt(2));
            } 
            rs.close();
            st.close();
            con.close();
            

        } 
         catch (Exception ee)
         {
            System.out.print(ee.getMessage());
        } 
    }
    
    
    // disconnect to MySQL  
    void deconnSQL() {  
        try {  
            if (conn != null){
            	conn.close(); 
            	conn = null;
            }  
                
        } catch (Exception e) {  
            System.out.println("关闭数据库问题 ：");  
            e.printStackTrace();  
        }  
    }  
  
    // execute selection language  
    ResultSet selectSQL(String sql) {  
        ResultSet rs = null;  
        try {  
            statement = conn.createStatement();  
            rs = statement.executeQuery(sql);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return rs;  
    }  
  
    // execute insertion language  
    boolean insertSQL(String sql) {  
        try {  
            statement = conn.createStatement();
            statement.executeUpdate(sql);  
            return true;  
        } catch (SQLException e) {  
            System.out.println("插入数据库时出错：");  
            e.printStackTrace();  
        } catch (Exception e) {  
            System.out.println("插入时出错：");  
            e.printStackTrace();  
        }  
        return false;  
    }  
    //execute delete language  
    boolean deleteSQL(String sql) {  
        try {  
        	statement = conn.createStatement();
            statement.executeUpdate(sql); 
            return true;  
        } catch (SQLException e) {  
            System.out.println("插入数据库时出错：");  
            e.printStackTrace();  
        } catch (Exception e) {  
            System.out.println("插入时出错：");  
            e.printStackTrace();  
        }  
        return false;  
    }  
    //execute update language  
    boolean updateSQL(String sql) {  
        try {  
        	statement = conn.createStatement();
            statement.executeUpdate(sql);
            return true;  
        } catch (SQLException e) {  
            System.out.println("插入数据库时出错：");  
            e.printStackTrace();  
        } catch (Exception e) {  
            System.out.println("插入时出错：");  
            e.printStackTrace();  
        }  
        return false;  
    }  
}